-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:插入数据触发trigger,before

--step1:建表，插入数据;expect:成功
drop table if exists fvt_trigger_table_yf_0009_1;
create table fvt_trigger_table_yf_0009_1(id int,name varchar(10),sal int) with (storage_type=ustore)
partition by range(sal)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
insert into fvt_trigger_table_yf_0009_1 values(1,'xiaohong',100);
insert into fvt_trigger_table_yf_0009_1 values(2,'xiaowang',200);
insert into fvt_trigger_table_yf_0009_1 values(3,'xiaolei',300);
insert into fvt_trigger_table_yf_0009_1 values(4,'xiaozhang',400);
insert into fvt_trigger_table_yf_0009_1 values(5,'xili',600);
drop table if exists fvt_trigger_table_yf_0009_2;
create table fvt_trigger_table_yf_0009_2(id int,name varchar(10),sal int) with (storage_type=ustore)
partition by range(sal)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
insert into fvt_trigger_table_yf_0009_2 values(1,'ss',444);
drop table if exists fvt_trigger_table_yf_0009_3;
create table fvt_trigger_table_yf_0009_3(sal int) with (storage_type=ustore)
partition by range(sal)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
insert into fvt_trigger_table_yf_0009_3 values(100),(200),(100),(500),(200);

--step2:创建函数，创建触发器;expect:成功
create or replace function trigger_fun_0009()
returns trigger as $$
declare
a int;
b int;
l sys_refcursor;
v fvt_trigger_table_yf_0009_1.id%type;
cursor k is select fvt_trigger_table_yf_0009_1.id from fvt_trigger_table_yf_0009_1,fvt_trigger_table_yf_0009_2 where fvt_trigger_table_yf_0009_1.id>fvt_trigger_table_yf_0009_2.id and length(fvt_trigger_table_yf_0009_1.name)>4;
begin
a:=1;
b:=0;
for i in (select id from fvt_trigger_table_yf_0009_1 where sal > (select avg(sal) from fvt_trigger_table_yf_0009_3) )
  loop
     a:=a+1;
    if
       a<4 then
       insert into fvt_trigger_table_yf_0009_2 values(4,'as',40);

   else
            b:=b+1;
                 for i in k loop
                   open l for select id from fvt_trigger_table_yf_0009_1;
                      fetch l into v;
                       close l;
                 end loop;
   end if;
  end loop;
return new;
end;
$$language plpgsql;
/
create trigger fvt_trigger_yf_t_0009 before insert on fvt_trigger_table_yf_0009_1
for each row execute procedure trigger_fun_0009();
/
--step3:插入数据，触发trigger,查询表;expect:成功
insert into fvt_trigger_table_yf_0009_1 values(6,'xiaochen',1200);
select * from fvt_trigger_table_yf_0009_1 order by id;
select * from fvt_trigger_table_yf_0009_2 order by id;
select * from fvt_trigger_table_yf_0009_3 order by sal;

--step4:环境清理;expect:成功
drop trigger fvt_trigger_yf_t_0009 on fvt_trigger_table_yf_0009_1 cascade;
drop table fvt_trigger_table_yf_0009_1 cascade;
drop table fvt_trigger_table_yf_0009_2 cascade;
drop table fvt_trigger_table_yf_0009_3 cascade;
drop function trigger_fun_0009;